package com.dys.dao.impl;

import com.dys.dao.TeacherDao;
import com.dys.entity.Department;
import com.dys.entity.Teacher;
import com.dys.util.ConnectionPool;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: 疾风
 * @ClassName: TeacherDaoImpl
 * @Description: 教师DAO实现类
 * @CreateDate: 2024/10/11
 * @Version: v1.0.0
 */
public class TeacherDaoImpl implements TeacherDao {
    @Override
    public List<Teacher> selectTeachersByPage(int pageNo, int pageSize, String teacherName, String phone) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Teacher> list = new ArrayList<>();
        try {
            // 预编译SQL
            // 预编译sql:
            String sql = "select * from t_teacher t join t_department d on t.did = d.id where 1=1";
            if (!"".equals(teacherName) && teacherName != null) {
                sql += " and t.teacher_name like '%" + teacherName + "%'";
            }
            if (!"".equals(phone) && phone != null) {
                sql += " and t.phone like '%" + phone + "%'";
            }
            sql += " limit ?,? ";
            ps = conn.prepareStatement(sql);
            // 设置参数
            ps.setInt(1, pageNo);
            ps.setInt(2, pageSize);

            System.out.println("分页查询教师SQL: " + sql);
            // 执行SQL
            rs = ps.executeQuery();
            // 处理结果
            while (rs.next()) {
                // 获取教师相关属性
                int tid = rs.getInt("tid");
                String tName = rs.getString("teacher_name");
                String sex = rs.getString("sex");
                String hobby = rs.getString("hobby");
                Date birthDate = rs.getDate("birth_date");
                String tPhone = rs.getString("phone");
                String tDesc = rs.getString("teacher_desc");
                int did = rs.getInt("did");
                // 获取部门相关属性
                int id = rs.getInt("id");
                String name = rs.getString("name");
                // 封装部门对象
                Department department = new Department(id, name);
                // 封装教师对象
                Teacher teacher = new Teacher(tid, tName, sex, hobby, birthDate, tPhone, tDesc, did, department);
                // 将教师对象添加到集合
                list.add(teacher);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return list;
    }

    @Override
    public int selectTeacherCount() {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select count(*) from t_teacher");
            // 执行SQL
            rs = ps.executeQuery();
            // 获取总记录数
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return count;
    }

    @Override
    public int deleteOneTeacherInfo(int tid) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("delete from t_teacher where tid=?");
            ps.setInt(1, tid);
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }

    @Override
    public int saveOneTeacherInfo(Teacher teacher) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("insert into t_teacher values(default,?,?,?,?,?,?,?)");
            // 设置预编译参数
            ps.setString(1, teacher.getTeacherName());
            ps.setString(2, teacher.getSex());
            ps.setString(3, teacher.getHobby());
            ps.setObject(4, teacher.getBirthDate());
            ps.setString(5, teacher.getPhone());
            ps.setString(6, teacher.getTeacherDesc());
            ps.setInt(7, teacher.getDid());
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }

    @Override
    public int updateOneTeacherInfo(Teacher teacher) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("update t_teacher set teacher_name=?,sex=?,hobby=?,birth_date=?,phone=?,teacher_desc=?,did=? where tid=?");
            ps.setString(1, teacher.getTeacherName());
            ps.setString(2, teacher.getSex());
            ps.setString(3, teacher.getHobby());
            ps.setObject(4, teacher.getBirthDate());
            ps.setString(5, teacher.getPhone());
            ps.setString(6, teacher.getTeacherDesc());
            ps.setInt(7, teacher.getDid());
            ps.setInt(8, teacher.getTid());
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }

    @Override
    public Teacher selectOneTeacherInfo(int tid) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Teacher teacher = null;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select * from t_teacher where tid=?");
            ps.setInt(1, tid);
            // 执行SQL
            rs = ps.executeQuery();
            // 获取班级信息，封装对象
            if (rs.next()) {
                int id = rs.getInt("tid");
                String teacherName = rs.getString("teacher_name");
                String sex = rs.getString("sex");
                String hobby = rs.getString("hobby");
                Date birthdate = rs.getDate("birth_date");
                String phone = rs.getString("phone");
                String teacherDesc = rs.getString("teacher_desc");
                int did = rs.getInt("did");
                teacher = new Teacher(id, teacherName, sex, hobby, birthdate, phone, teacherDesc, did);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return teacher;
    }
}
